package Util;

import EncapsulatingObjects.Trip;
import EncapsulatingObjects.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ShoppingCartUtil {
    public Connection getConnection() {
        Connection conn = null;
        //加载一个驱动器
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            //定义一个链接
            String url = "jdbc:derby://localhost:1527/NIIT";
            String user = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    public void createTable() throws SQLException {
        Connection conn = getConnection();
        String str1 = "create table shopping_cart (userid int, primary key (userid))";
        Statement stmt = null;
        stmt = conn.createStatement();
        stmt.execute(str1);
        stmt.close();
    }

    /**
     * 该方法只在最初被执行一次
     */
    public void initTrips() throws SQLException {
        Connection conn = getConnection();
        Statement stmt = conn.createStatement();
        TripDatabaseUtil td = new TripDatabaseUtil();
        List<Trip> trips = td.quaryAll();
        for (int i = 0; i < trips.size(); i++) {
            String columnName = "Trip_" + trips.get(i).getId();
            stmt.execute("alter table shopping_cart add column " + columnName + " int default 1");
        }
        stmt.close();
    }
    /**
     * 当管理员添加新的旅游时，调用该方法，在表中新增列
     * @param trip
     */
    public void addTrips(Trip trip) {
        Connection conn = getConnection();
        try {
            Statement stmt = conn.createStatement();
            String columnName = "Trip_" + trip.getId();
            String updateTrip = "alter table shopping_cart add column " + columnName + " int default 1";
            stmt.execute(updateTrip);
            stmt.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public void dropTrip(Trip trip){
        Connection conn = getConnection();
        try {
            Statement stmt = conn.createStatement();
            String sql = "ALTER TABLE shopping_cart DROP COLUMN trip_"+trip.getId();
            stmt.execute(sql);
            stmt.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    /**
     * 返回当前用户所有未支付订单对应的旅游编号，该方法配合quaryAll使用
     */
    public int[] viewMyCart(User user, int s) {
        Connection conn = getConnection();
        ArrayList<String> tripsName = new ArrayList<>();
        int[] tripsId = new int[100];
        int column = 0;
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from shopping_cart");
            while (rs.next()) {
                if (rs.getInt(1) == user.getId()) {
                    ResultSetMetaData md = rs.getMetaData();
                    column = md.getColumnCount();

                    for (int i = 2; i <= column; i++) {
                        if (rs.getInt(i) == s)//添加到购物车中未支付的为2
                            tripsName.add(md.getColumnName(i));
                    }
                    break;
                }
            }
            tripsId = new int[tripsName.size()];
            for (int i = 0; i < tripsName.size(); i++) {
                String[] str = tripsName.get(i).split("_");
                tripsId[i] = Integer.parseInt(str[1]);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return tripsId;
    }

    /**
     * 每当有新用户注册时，将其添加到该表中
     */
    public void addUser(User user){
        Connection conn = getConnection();
        try {
            Statement stmt = conn.createStatement();
            stmt.execute("insert into shopping_cart (userid) values (" + user.getId() + ")");
            stmt.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 当用户向购物车中添加旅游时，把改旅游所对的值改为true
     */
    public void updateTripToCart(User user, Trip trip, int statue) {
        Connection conn = getConnection();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from shopping_cart");
            stmt.execute("update shopping_cart set trip_" + trip.getId() + " = " + statue +" where userid = " + user.getId());
            stmt.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void getTripId(Trip trip) {
        try {
            Connection conn = getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from tripdata");
            while (rs.next()){
                if(rs.getString("tripname").equals(trip.getName())){
                    trip.setId(rs.getInt(1));
                    break;
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
